using Lzfy_His_Service.Models;
using Microsoft.Data.SqlClient;
using Serilog;
using System.Data;
using System.Reflection.Metadata;

namespace Lzfy_His_Service.Services
{
    /// <summary>
    /// 坐席账号管理服务实现
    /// </summary>
    public class AgentAccountService : IAgentAccountService
    {
        private readonly string _connectionString;
        private readonly IConfiguration _configuration;
        private readonly ILogger<AgentAccountService> _logger;

        // 配置项
        private int InactiveMinutes => _configuration.GetValue<int>("AgentAccount:InactiveMinutes", 30);
        private int UsageDurationMinutes => _configuration.GetValue<int>("AgentAccount:UsageDurationMinutes", 120);
        private int CleanupIntervalMinutes => _configuration.GetValue<int>("AgentAccount:CleanupIntervalMinutes", 60);

        public AgentAccountService(
            IConfiguration configuration,
            ILogger<AgentAccountService> logger)
        {
            _configuration = configuration;
            _logger = logger;
            _connectionString = _configuration.GetConnectionString("DefaultConnection") 
                ?? throw new InvalidOperationException("数据库连接字符串未配置");
        }

        /// <summary>
        /// 申请坐席账号使用权
        /// </summary>
        public async Task<AgentAccountResponse?> ApplyAccountAsync(AgentAccountApplyRequest request)
        {
            try
            {
                using var connection = new SqlConnection(_connectionString);
                await connection.OpenAsync();
                using var transaction = connection.BeginTransaction();

                try
                {
                    // 检查用户是否已经申请过有效的账号（排除不活跃和过期的账号）
                    var currentTime = DateTime.Now;
                    var inactiveThreshold = currentTime.AddMinutes(-InactiveMinutes);
                    
                    var existingAccountSql = @"
                        SELECT COUNT(*) FROM lzfy_agent_account 
                        WHERE username = @username AND Deleted = 0 
                        AND (
                            -- 有锁定且未确认的账号（申请中）
                            (rowLock IS NOT NULL AND LoginDate IS NULL)
                            OR 
                            -- 有活跃的已确认账号
                            (LoginDate IS NOT NULL AND LogoutDate > @currentTime AND 
                             (LastActionDate IS NULL OR LastActionDate >= @inactiveThreshold))
                        )";
                    
                    using var existingCmd = new SqlCommand(existingAccountSql, connection, transaction);
                    existingCmd.Parameters.AddWithValue("@username", request.Username);
                    existingCmd.Parameters.AddWithValue("@currentTime", currentTime);
                    existingCmd.Parameters.AddWithValue("@inactiveThreshold", inactiveThreshold);
                    var existingCount = (int)(await existingCmd.ExecuteScalarAsync() ?? 0);
                    
                    if (existingCount > 0)
                    {
                        _logger.LogWarning("用户 {Username} 已经申请过坐席账号或有账号正在申请中", request.Username);
                        await transaction.RollbackAsync();
                        // 返回特殊标识，表示用户已申请
                        return new AgentAccountResponse
                        {
                            Id = -1, // 特殊标识
                            ApiUrl = "USER_ALREADY_APPLIED",
                            FromExten = null,
                            Exten = null,
                            AccountId = null,
                            ApiSecret = null
                        };
                    }
                    
                    // 如果用户有不活跃或过期的账号，先清理这些账号
                    // 首先查询要清理的账号信息，用于API调用
                    var queryExpiredSql = @"
                        SELECT id, ACCOUNTID, Exten, APISecret, apiurl
                        FROM lzfy_agent_account 
                        WHERE Deleted = 0 
                        AND (
                            -- 过期的账号
                            (LogoutDate IS NOT NULL AND LogoutDate <= @currentTime)
                            OR 
                            -- 不活跃的账号
                            (LastActionDate IS NOT NULL AND LastActionDate < @inactiveThreshold)
                        )";
                    
                    var expiredAccounts = new List<AgentAccountResponse>();
                    using var queryCmd = new SqlCommand(queryExpiredSql, connection, transaction);
                    queryCmd.Parameters.AddWithValue("@username", request.Username);
                    queryCmd.Parameters.AddWithValue("@currentTime", currentTime);
                    queryCmd.Parameters.AddWithValue("@inactiveThreshold", inactiveThreshold);
                    
                    using var queryReader = await queryCmd.ExecuteReaderAsync();
                    while (await queryReader.ReadAsync())
                    {
                        expiredAccounts.Add(new AgentAccountResponse
                        {
                            Id = queryReader.GetInt32("id"),
                            AccountId = queryReader.IsDBNull("ACCOUNTID") ? null : queryReader.GetString("ACCOUNTID"),
                            Exten = queryReader.IsDBNull("Exten") ? null : queryReader.GetString("Exten"),
                            ApiSecret = queryReader.IsDBNull("APISecret") ? null : queryReader.GetString("APISecret"),
                            ApiUrl = queryReader.IsDBNull("apiurl") ? null : queryReader.GetString("apiurl")
                        });
                    }
                    queryReader.Close();
                    
                    // 为每个过期账号调用API清理手机号
                    int apiCleanupCount = 0;
                    foreach (var account in expiredAccounts)
                    {
                        try
                        {
                            // 将AgentAccountResponse转换为AgentAccount
                            var agentAccount = new AgentAccount
                            {
                                Id = account.Id,
                                AccountId = account.AccountId,
                                Exten = account.Exten,
                                ApiSecret = account.ApiSecret,
                                ApiUrl = account.ApiUrl
                            };
                            
                            var success = await UpdateAgentMobileAsync(agentAccount, "");
                            if (success)
                            {
                                apiCleanupCount++;
                                _logger.LogInformation("成功清理坐席账号 {AccountId} (分机号: {Exten}) 的手机号", account.AccountId, account.Exten);
                            }
                            else
                            {
                                _logger.LogWarning("清理坐席账号 {AccountId} (分机号: {Exten}) 的手机号失败", account.AccountId, account.Exten);
                            }
                        }
                        catch (Exception ex)
                        {
                            _logger.LogError(ex, "调用API清理坐席账号 {AccountId} (分机号: {Exten}) 手机号时发生错误", account.AccountId, account.Exten);
                        }
                    }
                    
                    // 然后清理数据库中的账号记录
                    var cleanupSql = @"
                        UPDATE lzfy_agent_account 
                        SET username = NULL, fullname = NULL, userPhone = NULL, 
                            LoginDate = NULL, LogoutDate = NULL, LastActionDate = NULL, 
                            rowLock = NULL, rowLockDate = NULL
                        WHERE Deleted = 0 
                        AND (
                            -- 过期的账号
                            (LogoutDate IS NOT NULL AND LogoutDate <= @currentTime)
                            OR 
                            -- 不活跃的账号
                            (LastActionDate IS NOT NULL AND LastActionDate < @inactiveThreshold)
                        )";
                    
                    using var cleanupCmd = new SqlCommand(cleanupSql, connection, transaction);
                    cleanupCmd.Parameters.AddWithValue("@username", request.Username);
                    cleanupCmd.Parameters.AddWithValue("@currentTime", currentTime);
                    cleanupCmd.Parameters.AddWithValue("@inactiveThreshold", inactiveThreshold);
                    var cleanedCount = await cleanupCmd.ExecuteNonQueryAsync();
                    
                    if (cleanedCount > 0)
                    {
                        _logger.LogInformation("用户 {Username} 的 {Count} 个不活跃或过期坐席账号已被清理，其中 {ApiCleanupCount} 个成功调用API清理手机号", request.Username, cleanedCount, apiCleanupCount);
                    }

                    // 查找可用的坐席账号
                    // currentTime 和 inactiveThreshold 已在上面定义
                    
                    var findAccountSql = @"
                        SELECT TOP 1 id, apiurl, FromExten, Exten, ACCOUNTID, APISecret
                        FROM lzfy_agent_account 
                        WHERE Deleted = 0 
                        AND rowLock IS NULL
                        AND (
                            (LastActionDate IS NULL OR LastActionDate < @inactiveThreshold)
                            OR 
                            (LogoutDate IS NOT NULL AND LogoutDate < @currentTime)
                        )
                        ORDER BY ISNULL(LastActionDate, '1900-01-01')";
                    
                    using var findCmd = new SqlCommand(findAccountSql, connection, transaction);
                    findCmd.Parameters.AddWithValue("@inactiveThreshold", inactiveThreshold);
                    findCmd.Parameters.AddWithValue("@currentTime", currentTime);
                    
                    using var reader = await findCmd.ExecuteReaderAsync();
                    
                    if (!reader.Read())
                    {
                        _logger.LogWarning("当前没有可用的坐席账号");
                        reader.Close();
                        await transaction.RollbackAsync();
                        return null;
                    }
                    
                    var accountId = reader.GetInt32("id"); //好像匹配错了
                    var apiUrl = reader.IsDBNull("apiurl") ? null : reader.GetString("apiurl");
                    var fromExten = reader.IsDBNull("FromExten") ? null : reader.GetString("FromExten");
                    var exten = reader.IsDBNull("Exten") ? null : reader.GetString("Exten");
                    var accountIdStr = reader.IsDBNull("ACCOUNTID") ? null : reader.GetString("ACCOUNTID");
                    var apiSecret = reader.IsDBNull("APISecret") ? null : reader.GetString("APISecret");
                    
                    reader.Close();
                    
                    // 锁定该账号并设置用户信息
                    var lockGuid = Guid.NewGuid().ToString();
                    var lockAccountSql = @"
                        UPDATE lzfy_agent_account 
                        SET rowLock = @lockGuid, username = @username, fullname = @fullName, userPhone = @userPhone, rowLockDate = @lockDate
                        WHERE id = @accountId AND rowLock IS NULL";
                    
                    using var lockCmd = new SqlCommand(lockAccountSql, connection, transaction);
                    lockCmd.Parameters.AddWithValue("@lockGuid", lockGuid);
                    lockCmd.Parameters.AddWithValue("@accountId", accountId);
                    lockCmd.Parameters.AddWithValue("@username", request.Username);
                    lockCmd.Parameters.AddWithValue("@fullName", request.FullName ?? (object)DBNull.Value);
                    lockCmd.Parameters.AddWithValue("@userPhone", request.UserPhone ?? (object)DBNull.Value);
                    lockCmd.Parameters.AddWithValue("@lockDate", currentTime);
                    
                    var lockResult = await lockCmd.ExecuteNonQueryAsync();
                    if (lockResult == 0)
                    {
                        _logger.LogWarning("账号 {AccountId} 锁定失败，可能已被其他用户申请", accountId);
                        await transaction.RollbackAsync();
                        return null;
                    }
                    
                    await transaction.CommitAsync();
                    
                    _logger.LogInformation("用户 {Username} 成功申请到坐席账号 {AccountId}", request.Username, accountId);
                    
                    var response = new AgentAccountResponse
                    {
                        Id = accountId,
                        ApiUrl = apiUrl,
                        FromExten = fromExten,
                        Exten = exten,
                        AccountId = accountIdStr,
                        ApiSecret = apiSecret,
                        Username = request.Username,
                        FullName = request.FullName
                    };
                    
                    // 如果清理了不活跃账号，在ApiUrl中添加特殊标识
                    if (cleanedCount > 0)
                    {
                        response.ApiUrl = $"CLEANED_INACTIVE:{response.ApiUrl}";
                    }
                    
                    return response;
                }
                catch (Exception)
                {
                    try
                    {
                        if (transaction.Connection != null)
                        {
                            await transaction.RollbackAsync();
                        }
                    }
                    catch (Exception rollbackEx)
                    {
                        _logger.LogError(rollbackEx, "事务回滚时发生错误");
                    }
                    throw;
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "申请坐席账号时发生错误: {Username}", request.Username);
                return null;
            }
        }

        /// <summary>
        /// 确认或取消确认坐席账号使用权
        /// </summary>
        public async Task<bool> ConfirmAccountAsync(AgentAccountConfirmRequest request)
        {
            try
            {
                using var connection = new SqlConnection(_connectionString);
                await connection.OpenAsync();
                
                var currentTime = DateTime.Now;
                
                if (request.IsConfirm)
                {
                    // 确认使用
                    var logoutTime = currentTime.AddMinutes(UsageDurationMinutes);
                    
                    var confirmSql = @"
                        UPDATE lzfy_agent_account 
                        SET LoginDate = @loginDate,
                            LogoutDate = @logoutDate,
                            LastActionDate = @lastActionDate,
                            username = @username,
                            userPhone = @userPhone,
                            rowLock = NULL,
                            rowLockDate = NULL
                        WHERE id = @accountId AND rowLock IS NOT NULL AND username = @username";
                    
                    using var confirmCmd = new SqlCommand(confirmSql, connection);
                    confirmCmd.Parameters.AddWithValue("@loginDate", currentTime);
                    confirmCmd.Parameters.AddWithValue("@logoutDate", logoutTime);
                    confirmCmd.Parameters.AddWithValue("@lastActionDate", currentTime);
                    confirmCmd.Parameters.AddWithValue("@username", request.Username);
                    confirmCmd.Parameters.AddWithValue("@userPhone", request.UserPhone ?? (object)DBNull.Value);
                    confirmCmd.Parameters.AddWithValue("@accountId", request.AccountId);
                    
                    var result = await confirmCmd.ExecuteNonQueryAsync();
                    
                    if (result > 0)
                    {
                        _logger.LogInformation("用户 {Username} 确认使用坐席账号 {AccountId}，使用时长 {Duration} 分钟", 
                            request.Username, request.AccountId, UsageDurationMinutes);
                        return true;
                    }
                }
                else
                {
                    // 取消确认，解锁账号
                    var cancelSql = @"
                        UPDATE lzfy_agent_account 
                        SET rowLock = NULL, username = NULL, fullname = NULL, rowLockDate = NULL
                        WHERE id = @accountId AND username = @username";
                    
                    using var cancelCmd = new SqlCommand(cancelSql, connection);
                    cancelCmd.Parameters.AddWithValue("@accountId", request.AccountId);
                    cancelCmd.Parameters.AddWithValue("@username", request.Username);
                    
                    var result = await cancelCmd.ExecuteNonQueryAsync();
                    
                    if (result > 0)
                    {
                        _logger.LogInformation("用户 {Username} 取消确认坐席账号 {AccountId}", 
                            request.Username, request.AccountId);
                        return true;
                    }
                }
                
                return false;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "确认坐席账号时发生错误: {Username}, AccountId: {AccountId}, IsConfirm: {IsConfirm}", 
                    request.Username, request.AccountId, request.IsConfirm);
                return false;
            }
        }

        /// <summary>
        /// 记录用户操作时间
        /// </summary>
        public async Task<bool> RecordActionAsync(AgentAccountActionRequest request)
        {
            try
            {
                using var connection = new SqlConnection(_connectionString);
                await connection.OpenAsync();
                
                var currentTime = DateTime.Now;
                
                var updateSql = @"
                    UPDATE lzfy_agent_account 
                    SET LastActionDate = @lastActionDate
                    WHERE id = @accountId 
                    AND username = @username 
                    AND LogoutDate > @currentTime
                    AND Deleted = 0";
                
                using var updateCmd = new SqlCommand(updateSql, connection);
                updateCmd.Parameters.AddWithValue("@lastActionDate", currentTime);
                updateCmd.Parameters.AddWithValue("@accountId", request.AccountId);
                updateCmd.Parameters.AddWithValue("@username", request.Username);
                updateCmd.Parameters.AddWithValue("@currentTime", currentTime);
                
                var result = await updateCmd.ExecuteNonQueryAsync();
                
                if (result > 0)
                {
                    _logger.LogDebug("用户 {Username} 更新坐席账号 {AccountId} 操作时间", 
                        request.Username, request.AccountId);
                    return true;
                }
                else
                {
                    _logger.LogWarning("用户 {Username} 更新坐席账号 {AccountId} 操作时间失败，可能账号已过期或用户不匹配", 
                        request.Username, request.AccountId);
                    return false;
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "记录用户操作时间时发生错误: {Username}, AccountId: {AccountId}", 
                    request.Username, request.AccountId);
                return false;
            }
        }

        /// <summary>
        /// 获取用户当前使用的坐席账号
        /// </summary>
        public async Task<AgentAccountResponse?> GetUserCurrentAccountAsync(string username)
        {
            try
            {
                using var connection = new SqlConnection(_connectionString);
                await connection.OpenAsync();
                
                var currentTime = DateTime.Now;
                
                var sql = @"
                    SELECT id, apiurl, FromExten, Exten, ACCOUNTID, APISecret, 
                           LoginDate, LogoutDate, LastActionDate, username, fullname, userPhone
                    FROM lzfy_agent_account 
                    WHERE username = @username 
                    AND Deleted = 0 
                    AND LoginDate IS NOT NULL 
                    AND LogoutDate > @currentTime";
                
                using var cmd = new SqlCommand(sql, connection);
                cmd.Parameters.AddWithValue("@username", username);
                cmd.Parameters.AddWithValue("@currentTime", currentTime);
                
                using var reader = await cmd.ExecuteReaderAsync();
                
                if (reader.Read())
                {
                    return new AgentAccountResponse
                    {
                        Id = reader.GetInt32("id"),
                        ApiUrl = reader.IsDBNull("apiurl") ? null : reader.GetString("apiurl"),
                        FromExten = reader.IsDBNull("FromExten") ? null : reader.GetString("FromExten"),
                        Exten = reader.IsDBNull("Exten") ? null : reader.GetString("Exten"),
                        AccountId = reader.IsDBNull("ACCOUNTID") ? null : reader.GetString("ACCOUNTID"),
                        ApiSecret = reader.IsDBNull("APISecret") ? null : reader.GetString("APISecret"),
                        LoginDate = reader.IsDBNull("LoginDate") ? null : reader.GetDateTime("LoginDate"),
                        LogoutDate = reader.IsDBNull("LogoutDate") ? null : reader.GetDateTime("LogoutDate"),
                        LastActionDate = reader.IsDBNull("LastActionDate") ? null : reader.GetDateTime("LastActionDate"),
                        Username = reader.IsDBNull("username") ? null : reader.GetString("username"),
                        FullName = reader.IsDBNull("fullname") ? null : reader.GetString("fullname"),
                        UserPhone = reader.IsDBNull("userPhone") ? null : reader.GetString("userPhone")
                    };
                }
                
                return null;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取用户当前坐席账号时发生错误: {Username}", username);
                return null;
            }
        }
            
        /// <summary>
        /// 清理坐席账号手机号码
        /// </summary>
        /// <returns></returns>
        public async Task<int> CleanupAccountsPhoneAsync()
        {
            try
            { 
                using var connection = new SqlConnection(_connectionString);
                await connection.OpenAsync();
                
                var currentTime = DateTime.Now;
                var lockGuid = Guid.NewGuid().ToString();
                var clareSql = @"
                    UPDATE lzfy_agent_account 
                    SET rowLock = @lockGuid,
                        rowLockDate = @currentTime,
                        username = 'admin',
                        fullname = '管理员'
                    WHERE logindate IS NUll
                    and rowLock IS NULl
                    AND Deleted = 0";
                using var clareCmd = new SqlCommand(clareSql, connection);
                clareCmd.Parameters.AddWithValue("@lockGuid", lockGuid);
                clareCmd.Parameters.AddWithValue("@currentTime", currentTime);
                var clearResult = await clareCmd.ExecuteNonQueryAsync();
                if (clearResult > 0)
                {
                    //_Logger.LogInformation("锁定了 {Count} 个未使用的坐席账号", cleaeResult);
                    _logger.LogInformation("锁定了 {Count} 个未使用的坐席账号", clearResult);

                    var queryAccountSql = @"
                        SELECT id, apiurl, FromExten, Exten, ACCOUNTID, APISecret, username, fullname, userPhone
                        FROM lzfy_agent_account 
                        WHERE username = 'admin'
                        AND Deleted = 0";
                    using var queryCmd = new SqlCommand(queryAccountSql, connection);
                    //queryCmd.Parameters.AddWithValue("@lockGuid", lockGuid);
                    var queryAccounts = new List<AgentAccount>();
                    using var reader = await queryCmd.ExecuteReaderAsync();
                    while (await reader.ReadAsync())
                    {
                        queryAccounts.Add(new AgentAccount
                        {
                            Id = reader.GetInt32("id"),
                            ApiUrl = reader.IsDBNull("apiurl") ? null : reader.GetString("apiurl"),
                            Exten = reader.IsDBNull("Exten") ? null : reader.GetString("Exten"),
                            AccountId = reader.IsDBNull("ACCOUNTID") ? null : reader.GetString("ACCOUNTID"),
                            ApiSecret = reader.IsDBNull("APISecret") ? null : reader.GetString("APISecret")
                        });
                    }
                    await reader.CloseAsync();
                    // 为每个过期账号调用API清理手机号
                    int apiCleanupCount = 0;
                    int totalCount = queryAccounts.Count;
                    int processedCount = 0;
                    
                    foreach (var account in queryAccounts)
                    {
                        try
                        {
                            // 计算延迟时间：每分钟6次，即每10秒一次
                            // 根据已处理的账号数量计算延迟
                            if (processedCount > 0)
                            {
                                int delaySeconds = (processedCount / 6) * 60; // 每6个账号延迟1分钟
                                if (processedCount % 6 == 0)
                                {
                                    _logger.LogInformation("已处理 {ProcessedCount}/{TotalCount} 个账号，等待 {DelaySeconds} 秒后继续...", 
                                        processedCount, totalCount, 60);
                                    await Task.Delay(TimeSpan.FromSeconds(60));
                                }
                                else
                                {
                                    int remainingDelay = 10 - (int)((DateTime.Now - currentTime).TotalSeconds % 10);
                                    if (remainingDelay > 0 && remainingDelay < 10)
                                    {
                                        await Task.Delay(TimeSpan.FromSeconds(remainingDelay));
                                    }
                                }
                            }
                            
                            // 将AgentAccountResponse转换为AgentAccount
                            var agentAccount = new AgentAccount
                            {
                                Id = account.Id,
                                AccountId = account.AccountId,
                                Exten = account.Exten,
                                ApiSecret = account.ApiSecret,
                                ApiUrl = account.ApiUrl
                            };
                            
                            var success = await UpdateAgentMobileAsync(agentAccount, "");
                            if (success)
                            {
                                apiCleanupCount++;
                                _logger.LogInformation("成功清理坐席账号 {AccountId} (分机号: {Exten}) 的手机号，进度: {Processed}/{Total}", 
                                    account.AccountId, account.Exten, processedCount + 1, totalCount);
                            }
                            else
                            {
                                _logger.LogWarning("清理坐席账号 {AccountId} (分机号: {Exten}) 的手机号失败，进度: {Processed}/{Total}", 
                                    account.AccountId, account.Exten, processedCount + 1, totalCount);
                            }
                            var cleanupSql = @"
                            UPDATE lzfy_agent_account 
                            SET LoginDate = NULL,
                                LogoutDate = NULL,
                                LastActionDate = NULL,
                                username = NULL,
                                fullname = NULL,
                                userPhone = NULL,
                                rowLock = NULL,
                                rowLockDate = NULL
                            WHERE id = @id
                            AND rowLock is not null
                            AND Deleted = 0";
                            using var cleanupCmd = new SqlCommand(cleanupSql, connection);     
                            cleanupCmd.Parameters.AddWithValue("@id", account.Id);             
                            var cleanupResult = await cleanupCmd.ExecuteNonQueryAsync();
                            if (cleanupResult > 0)
                    {
                        _logger.LogInformation("调用API清理了手机号");
                    }   
                            processedCount++;
                        }
                        catch (Exception ex)
                        {
                            _logger.LogError(ex, "调用API清理坐席账号 {AccountId} (分机号: {Exten}) 手机号时发生错误，进度: {Processed}/{Total}", 
                                account.AccountId, account.Exten, processedCount + 1, totalCount);
                            processedCount++;
                        }
                    }
                    return apiCleanupCount;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "清理坐席账号手机号时发生错误");
                return 0; // 添加返回语句，确保所有路径都有返回值
            }
        }

        /// <summary>
        /// 定时清理过期的坐席账号权限
        /// </summary>
        public async Task<int> CleanupExpiredAccountsAsync()
        {
            try
            {
                using var connection = new SqlConnection(_connectionString);
                await connection.OpenAsync();
                
                var currentTime = DateTime.Now;
                var inactiveThreshold = currentTime.AddMinutes(-InactiveMinutes);
                var lockTimeoutThreshold = currentTime.AddMinutes(-1); // 锁定超过1分钟的阈值
                
                // 首先解锁超过appseting设置的过期时间的锁定记录
                var unlockSql = @"
                    UPDATE lzfy_agent_account 
                    SET rowLock = NULL,
                        username = NULL,
                        fullname = NULL,
                        rowLockDate = NULL
                    WHERE rowLock IS NOT NULL 
                    AND rowLockDate IS NOT NULL 
                    AND rowLockDate < @lockTimeoutThreshold
                    AND Deleted = 0";
                
                using var unlockCmd = new SqlCommand(unlockSql, connection);
                unlockCmd.Parameters.AddWithValue("@lockTimeoutThreshold", lockTimeoutThreshold);
                
                var unlockResult = await unlockCmd.ExecuteNonQueryAsync();
                
                if (unlockResult > 0)
                {
                    _logger.LogInformation("解锁了 {Count} 个超过1分钟的锁定坐席账号", unlockResult);
                }
                
                // 查询需要清理的过期坐席账号，先调用API清理手机号
                var queryExpiredSql = @"
                    SELECT id, apiurl, Exten, ACCOUNTID, APISecret
                    FROM lzfy_agent_account 
                    WHERE (
                        (LogoutDate IS NOT NULL AND LogoutDate < @currentTime) OR
                        (LastActionDate IS NOT NULL AND LastActionDate < @inactiveThreshold)
                    )
                    AND rowLock IS NULL
                    AND Deleted = 0
                    AND apiurl IS NOT NULL
                    AND ACCOUNTID IS NOT NULL
                    AND APISecret IS NOT NULL
                    AND Exten IS NOT NULL";
                
                using var queryCmd = new SqlCommand(queryExpiredSql, connection);
                queryCmd.Parameters.AddWithValue("@currentTime", currentTime);
                queryCmd.Parameters.AddWithValue("@inactiveThreshold", inactiveThreshold);
                
                var expiredAccounts = new List<AgentAccount>();
                using var reader = await queryCmd.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    expiredAccounts.Add(new AgentAccount
                    {
                        Id = reader.GetInt32("id"),
                        ApiUrl = reader.IsDBNull("apiurl") ? null : reader.GetString("apiurl"),
                        Exten = reader.IsDBNull("Exten") ? null : reader.GetString("Exten"),
                        AccountId = reader.IsDBNull("ACCOUNTID") ? null : reader.GetString("ACCOUNTID"),
                        ApiSecret = reader.IsDBNull("APISecret") ? null : reader.GetString("APISecret")
                    });
                }
                await reader.CloseAsync();
                
                // 为每个过期账号调用API清理手机号
                    int apiCleanupCount = 0;
                    foreach (var account in expiredAccounts)
                    {
                        try
                        {
                            // 将AgentAccountResponse转换为AgentAccount
                            var agentAccount = new AgentAccount
                            {
                                Id = account.Id,
                                AccountId = account.AccountId,
                                Exten = account.Exten,
                                ApiSecret = account.ApiSecret,
                                ApiUrl = account.ApiUrl
                            };
                            
                            var success = await UpdateAgentMobileAsync(agentAccount, "");
                            if (success)
                            {
                                apiCleanupCount++;
                                _logger.LogInformation("成功清理坐席账号 {AccountId} (分机号: {Exten}) 的手机号", account.AccountId, account.Exten);
                            }
                            else
                            {
                                _logger.LogWarning("清理坐席账号 {AccountId} (分机号: {Exten}) 的手机号失败", account.AccountId, account.Exten);
                            }
                        }
                        catch (Exception ex)
                        {
                            _logger.LogError(ex, "调用API清理坐席账号 {AccountId} (分机号: {Exten}) 手机号时发生错误", account.AccountId, account.Exten);
                        }
                    }
                
                // 然后清理过期的坐席账号权限
                var cleanupSql = @"
                    UPDATE lzfy_agent_account 
                    SET LoginDate = NULL,
                        LogoutDate = NULL,
                        LastActionDate = NULL,
                        username = NULL,
                        fullname = NULL,
                        userPhone = NULL
                    WHERE (
                        (LogoutDate IS NOT NULL AND LogoutDate < @currentTime) OR
                        (LastActionDate IS NOT NULL AND LastActionDate < @inactiveThreshold)
                    )
                    AND rowLock IS NULL
                    AND Deleted = 0";
                
                using var cleanupCmd = new SqlCommand(cleanupSql, connection);
                cleanupCmd.Parameters.AddWithValue("@currentTime", currentTime);
                cleanupCmd.Parameters.AddWithValue("@inactiveThreshold", inactiveThreshold);
                
                var cleanupResult = await cleanupCmd.ExecuteNonQueryAsync();
                
                if (cleanupResult > 0)
                {
                    _logger.LogInformation("清理了 {Count} 个过期的坐席账号权限（LogoutDate过期或LastActionDate超过{InactiveMinutes}分钟未操作），其中 {ApiCleanupCount} 个成功调用API清理了手机号", 
                        cleanupResult, InactiveMinutes, apiCleanupCount);
                }
                
                return unlockResult + cleanupResult;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "清理过期坐席账号权限时发生错误");
                return 0;
            }
        }
        
        /// <summary>
        /// 获取所有可用的坐席账号列表（管理员功能）
        /// </summary>
        public async Task<List<AgentAccountResponse>> GetAllAccountsAsync()
        {
            try
            {
                using var connection = new SqlConnection(_connectionString);
                await connection.OpenAsync();
                
                var sql = @"
                    SELECT id, apiurl, FromExten, Exten, ACCOUNTID, APISecret, 
                           LoginDate, LogoutDate, LastActionDate, username, fullname, userPhone, Deleted
                    FROM lzfy_agent_account 
                    WHERE Deleted = 0
                    ORDER BY id";
                
                using var cmd = new SqlCommand(sql, connection);
                using var reader = await cmd.ExecuteReaderAsync();
                
                var accounts = new List<AgentAccountResponse>();
                
                while (reader.Read())
                {
                    accounts.Add(new AgentAccountResponse
                    {
                        Id = reader.GetInt32("id"),
                        ApiUrl = reader.IsDBNull("apiurl") ? null : reader.GetString("apiurl"),
                        FromExten = reader.IsDBNull("FromExten") ? null : reader.GetString("FromExten"),
                        Exten = reader.IsDBNull("Exten") ? null : reader.GetString("Exten"),
                        AccountId = reader.IsDBNull("ACCOUNTID") ? null : reader.GetString("ACCOUNTID"),
                        ApiSecret = reader.IsDBNull("APISecret") ? null : reader.GetString("APISecret"),
                        LoginDate = reader.IsDBNull("LoginDate") ? null : reader.GetDateTime("LoginDate"),
                        LogoutDate = reader.IsDBNull("LogoutDate") ? null : reader.GetDateTime("LogoutDate"),
                        LastActionDate = reader.IsDBNull("LastActionDate") ? null : reader.GetDateTime("LastActionDate"),
                        Username = reader.IsDBNull("username") ? null : reader.GetString("username"),
                        FullName = reader.IsDBNull("fullname") ? null : reader.GetString("fullname"),
                        UserPhone = reader.IsDBNull("userPhone") ? null : reader.GetString("userPhone")
                    });
                }
                
                return accounts;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取所有坐席账号列表时发生错误");
                return new List<AgentAccountResponse>();
            }
        }

        /// <summary>
        /// 调用第三方API更新坐席手机号
        /// </summary>
        /// <param name="agentAccount">坐席账号信息</param>
        /// <param name="mobile">手机号（空字符串表示清理）</param>
        /// <returns>是否成功</returns>
        private async Task<bool> UpdateAgentMobileAsync(AgentAccount agentAccount, string mobile)
        {
            try
            {
                if (string.IsNullOrEmpty(agentAccount.ApiUrl) || 
                    string.IsNullOrEmpty(agentAccount.AccountId) || 
                    string.IsNullOrEmpty(agentAccount.ApiSecret) || 
                    string.IsNullOrEmpty(agentAccount.Exten))
                {
                    _logger.LogWarning("坐席账号 {AccountId} 缺少必要的API调用参数", agentAccount.AccountId);
                    return false;
                }

                // 生成时间戳和签名（模拟前端逻辑）
                var timestamp = DateTime.Now.ToString("yyyyMMddHHmmss");
                var sig = GenerateSignature(agentAccount.AccountId, agentAccount.ApiSecret, timestamp);
                var authorization = Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes($"{agentAccount.AccountId}:{timestamp}"));

                var requestBody = new
                {
                    exten = agentAccount.Exten,
                    mobile = mobile
                };

                using var httpClient = new HttpClient();
                httpClient.DefaultRequestHeaders.Add("Authorization", authorization);
                
                var jsonContent = System.Text.Json.JsonSerializer.Serialize(requestBody);
                var content = new StringContent(jsonContent, System.Text.Encoding.UTF8, "application/json");
                if (content.Headers.ContentType != null)
                {
                    content.Headers.ContentType.CharSet = "utf-8";
                }
                
                var url = $"{agentAccount.ApiUrl}v20160818/user/updateUser/{agentAccount.AccountId}?sig={sig}";
                
                _logger.LogDebug("调用API更新坐席手机号 - URL: {Url}, 请求体: {RequestBody}, Authorization: Basic {Authorization}", url, jsonContent, authorization);
                
                var response = await httpClient.PostAsync(url, content);
                var responseContent = await response.Content.ReadAsStringAsync();
                
                _logger.LogDebug("API响应 - 状态码: {StatusCode}, 响应内容: {ResponseContent}", response.StatusCode, responseContent);
                
                if (response.IsSuccessStatusCode)
                {
                    var responseData = System.Text.Json.JsonSerializer.Deserialize<Dictionary<string, object>>(responseContent);
                    
                    if (responseData != null && responseData.ContainsKey("code"))
                    {
                        var code = responseData["code"].ToString() ?? string.Empty;
                        if (code == "200")
                        {
                            _logger.LogDebug("成功调用API更新坐席 {AccountId} 手机号为: {Mobile}", agentAccount.AccountId, string.IsNullOrEmpty(mobile) ? "[清空]" : mobile);
                            return true;
                        }
                        else
                        {
                            _logger.LogWarning("API返回错误代码 {Code}，坐席 {AccountId}，响应内容: {ResponseContent}", code, agentAccount.AccountId, responseContent);
                            return false;
                        }
                    }
                    else
                    {
                        _logger.LogWarning("API响应格式异常，坐席 {AccountId}，响应内容: {ResponseContent}", agentAccount.AccountId, responseContent);
                        return false;
                    }
                }
                
                _logger.LogWarning("调用API更新坐席 {AccountId} 手机号失败，HTTP状态码: {StatusCode}，响应内容: {ResponseContent}", agentAccount.AccountId, response.StatusCode, responseContent);
                return false;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "调用API更新坐席 {AccountId} 手机号时发生异常", agentAccount.AccountId);
                return false;
            }
        }

        /// <summary>
        /// 生成API签名
        /// </summary>
        /// <param name="accountId">账户ID</param>
        /// <param name="apiSecret">API密钥</param>
        /// <param name="timestamp">时间戳</param>
        /// <returns>签名</returns>
        private string GenerateSignature(string accountId, string apiSecret, string timestamp)
        {
            try
            {
                var signString = $"{accountId}{apiSecret}{timestamp}";
                using var md5 = System.Security.Cryptography.MD5.Create();
                var hashBytes = md5.ComputeHash(System.Text.Encoding.UTF8.GetBytes(signString));
                return Convert.ToHexString(hashBytes).ToUpperInvariant();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "生成API签名时发生错误");
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取坐席账号统计信息
        /// </summary>
        public async Task<AgentAccountStatusResponse> GetAccountStatusAsync()
        {
            try
            {
                using var connection = new SqlConnection(_connectionString);
                await connection.OpenAsync();
                
                var currentTime = DateTime.Now;
                var inactiveThreshold = currentTime.AddMinutes(-InactiveMinutes);
                var lockTimeoutThreshold = currentTime.AddMinutes(-1);
                
                var sql = @"
                    SELECT 
                        -- 坐席总数
                        COUNT(*) as TotalAccounts,
                        
                        -- 使用中的坐席数量（已确认且未过期）
                        SUM(CASE 
                            WHEN LoginDate IS NOT NULL 
                                AND LogoutDate IS NOT NULL 
                                AND LogoutDate > @currentTime 
                                AND rowLock IS NULL
                            THEN 1 ELSE 0 END) as InUseAccounts,
                        
                        -- 申请中的坐席数量（已锁定但未确认）
                        SUM(CASE 
                            WHEN rowLock IS NOT NULL 
                                AND LoginDate IS NULL 
                                AND rowLockDate IS NOT NULL 
                                AND rowLockDate >= @lockTimeoutThreshold
                            THEN 1 ELSE 0 END) as PendingAccounts,
                        
                        -- 非活跃的坐席数量（超过非活跃时间阈值但未过期）
                        SUM(CASE 
                            WHEN LastActionDate IS NOT NULL 
                                AND LastActionDate < @inactiveThreshold 
                                AND (LogoutDate IS NULL OR LogoutDate > @currentTime)
                                AND rowLock IS NULL
                            THEN 1 ELSE 0 END) as InactiveAccounts,
                        
                        -- 过期的坐席数量（LogoutDate已过期）
                        SUM(CASE 
                            WHEN LogoutDate IS NOT NULL 
                                AND LogoutDate < @currentTime 
                                AND rowLock IS NULL
                            THEN 1 ELSE 0 END) as ExpiredAccounts,
                        
                        -- 锁定超时的坐席数量（rowLockDate超过1分钟）
                        SUM(CASE 
                            WHEN rowLock IS NOT NULL 
                                AND rowLockDate IS NOT NULL 
                                AND rowLockDate < @lockTimeoutThreshold
                            THEN 1 ELSE 0 END) as LockTimeoutAccounts
                    
                    FROM lzfy_agent_account 
                    WHERE Deleted = 0";
                
                using var cmd = new SqlCommand(sql, connection);
                cmd.Parameters.AddWithValue("@currentTime", currentTime);
                cmd.Parameters.AddWithValue("@inactiveThreshold", inactiveThreshold);
                cmd.Parameters.AddWithValue("@lockTimeoutThreshold", lockTimeoutThreshold);
                
                using var reader = await cmd.ExecuteReaderAsync();
                
                if (reader.Read())
                {
                    var totalAccounts = reader.GetInt32("TotalAccounts");
                    var inUseAccounts = reader.GetInt32("InUseAccounts");
                    var pendingAccounts = reader.GetInt32("PendingAccounts");
                    var inactiveAccounts = reader.GetInt32("InactiveAccounts");
                    var expiredAccounts = reader.GetInt32("ExpiredAccounts");
                    var lockTimeoutAccounts = reader.GetInt32("LockTimeoutAccounts");
                    
                    // 可申请的坐席数量 = 总数 - 使用中 - 申请中
                    var availableAccounts = totalAccounts - inUseAccounts - pendingAccounts;
                    
                    return new AgentAccountStatusResponse
                    {
                        TotalAccounts = totalAccounts,
                        InUseAccounts = inUseAccounts,
                        AvailableAccounts = Math.Max(0, availableAccounts), // 确保不为负数
                        PendingAccounts = pendingAccounts,
                        InactiveAccounts = inactiveAccounts,
                        ExpiredAccounts = expiredAccounts,
                        LockTimeoutAccounts = lockTimeoutAccounts,
                        StatisticsTime = currentTime,
                        InactiveMinutes = InactiveMinutes,
                        UsageDurationMinutes = UsageDurationMinutes
                    };
                }
                
                // 如果没有数据，返回默认值
                return new AgentAccountStatusResponse
                {
                    TotalAccounts = 0,
                    InUseAccounts = 0,
                    AvailableAccounts = 0,
                    PendingAccounts = 0,
                    InactiveAccounts = 0,
                    ExpiredAccounts = 0,
                    LockTimeoutAccounts = 0,
                    StatisticsTime = currentTime,
                    InactiveMinutes = InactiveMinutes,
                    UsageDurationMinutes = UsageDurationMinutes
                };            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取坐席使用状态统计时发生错误");
                
                // 发生错误时返回默认值
                return new AgentAccountStatusResponse
                {
                    TotalAccounts = 0,
                    InUseAccounts = 0,
                    AvailableAccounts = 0,
                    PendingAccounts = 0,
                    InactiveAccounts = 0,
                    ExpiredAccounts = 0,
                    LockTimeoutAccounts = 0,
                    StatisticsTime = DateTime.Now,
                    InactiveMinutes = InactiveMinutes,
                    UsageDurationMinutes = UsageDurationMinutes
                };
            }
        }

        /// <summary>
        /// 获取用户坐席申请状态
        /// </summary>
        public async Task<UserAgentStatusResponse> GetUserAgentStatusAsync(string username)
        {
            try
            {
                using var connection = new SqlConnection(_connectionString);
                await connection.OpenAsync();
                
                var currentTime = DateTime.Now;
                var lockTimeoutThreshold = currentTime.AddMinutes(-1);
                var inactiveThreshold = currentTime.AddMinutes(-InactiveMinutes);
                
                var sql = @"
                    SELECT id, apiurl, FromExten, Exten, ACCOUNTID, APISecret, 
                           LoginDate, LogoutDate, LastActionDate, username, fullname, userPhone,
                           rowLock, rowLockDate
                    FROM lzfy_agent_account 
                    WHERE username = @username 
                    AND Deleted = 0";
                
                using var cmd = new SqlCommand(sql, connection);
                cmd.Parameters.AddWithValue("@username", username);
                cmd.Parameters.AddWithValue("@currentTime", currentTime);
                cmd.Parameters.AddWithValue("@lockTimeoutThreshold", lockTimeoutThreshold);
                cmd.Parameters.AddWithValue("@inactiveThreshold", inactiveThreshold);
                
                using var reader = await cmd.ExecuteReaderAsync();
                
                if (reader.Read())
                {
                    var account = new AgentAccountResponse
                    {
                        Id = reader.GetInt32("id"),
                        ApiUrl = reader.IsDBNull("apiurl") ? null : reader.GetString("apiurl"),
                        FromExten = reader.IsDBNull("FromExten") ? null : reader.GetString("FromExten"),
                        Exten = reader.IsDBNull("Exten") ? null : reader.GetString("Exten"),
                        AccountId = reader.IsDBNull("ACCOUNTID") ? null : reader.GetString("ACCOUNTID"),
                        ApiSecret = reader.IsDBNull("APISecret") ? null : reader.GetString("APISecret"),
                        LoginDate = reader.IsDBNull("LoginDate") ? null : reader.GetDateTime("LoginDate"),
                        LogoutDate = reader.IsDBNull("LogoutDate") ? null : reader.GetDateTime("LogoutDate"),
                        LastActionDate = reader.IsDBNull("LastActionDate") ? null : reader.GetDateTime("LastActionDate"),
                        Username = reader.IsDBNull("username") ? null : reader.GetString("username"),
                        FullName = reader.IsDBNull("fullname") ? null : reader.GetString("fullname"),
                        UserPhone = reader.IsDBNull("userPhone") ? null : reader.GetString("userPhone")
                    };
                    
                    var rowLock = reader.IsDBNull("rowLock") ? null : reader.GetString("rowLock");
                    var rowLockDate = reader.IsDBNull("rowLockDate") ? (DateTime?)null : reader.GetDateTime("rowLockDate");
                    
                    // 判断账号状态
                    string accountStatus;
                    double? remainingLockMinutes = null;
                    double? remainingUsageMinutes = null;
                    
                    // 检查是否锁定超时
                    if (!string.IsNullOrEmpty(rowLock) && rowLockDate.HasValue && rowLockDate.Value < lockTimeoutThreshold)
                    {
                        accountStatus = "lock_timeout";
                    }
                    // 检查是否申请中（已锁定但未确认）
                    else if (!string.IsNullOrEmpty(rowLock) && !account.LoginDate.HasValue)
                    {
                        accountStatus = "pending";
                        if (rowLockDate.HasValue)
                        {
                            var lockElapsed = currentTime - rowLockDate.Value;
                            remainingLockMinutes = Math.Max(0, 1 - lockElapsed.TotalMinutes);
                        }
                    }
                    // 检查是否已过期（LogoutDate过期）
                    else if (account.LogoutDate.HasValue && account.LogoutDate.Value <= currentTime)
                    {
                        accountStatus = "expired";
                    }
                    // 检查是否因非活跃而过期（LastActionDate超过InactiveMinutes阈值）
                    else if (account.LastActionDate.HasValue && account.LastActionDate.Value < inactiveThreshold)
                    {
                        accountStatus = "inactive";
                        _logger.LogInformation("用户 {Username} 的坐席账号因非活跃而过期，最后操作时间: {LastActionDate}, 非活跃阈值: {InactiveThreshold}", 
                            username, account.LastActionDate.Value, inactiveThreshold);
                    }
                    // 检查是否使用中（已确认且未过期且活跃）
                    else if (account.LoginDate.HasValue && account.LogoutDate.HasValue && account.LogoutDate.Value > currentTime)
                    {
                        accountStatus = "active";
                        var usageRemaining = account.LogoutDate.Value - currentTime;
                        remainingUsageMinutes = Math.Max(0, usageRemaining.TotalMinutes);
                    }
                    else
                    {
                        accountStatus = "pending";
                    }
                    
                    return new UserAgentStatusResponse
                    {
                        HasAccount = true,
                        AccountStatus = accountStatus,
                        Account = account,
                        RemainingLockMinutes = remainingLockMinutes,
                        RemainingUsageMinutes = remainingUsageMinutes
                    };
                }
                
                // 用户没有坐席账号
                return new UserAgentStatusResponse
                {
                    HasAccount = false,
                    AccountStatus = "none",
                    Account = null,
                    RemainingLockMinutes = null,
                    RemainingUsageMinutes = null
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取用户坐席申请状态时发生错误: {Username}", username);
                
                // 发生错误时返回默认值
                return new UserAgentStatusResponse
                {
                    HasAccount = false,
                    AccountStatus = "none",
                    Account = null,
                    RemainingLockMinutes = null,
                    RemainingUsageMinutes = null
                };
            }
        }
    }
}